package dao;

import entity.Article;
import entity.Dept;
import entity.User;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import utils.DataSourceUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ListDao {
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());

    public List<User> findPage(User user) {
        try {
            return jdbcTemplate.query("SELECT * FROM `user`", BeanPropertyRowMapper.newInstance(User.class));
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 查询所有用户信息
     */
    public List<User> findAll(User user) {
        try {
            return jdbcTemplate.query("SELECT * FROM `user`", BeanPropertyRowMapper.newInstance(User.class));
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 根据搜索信息查找对应的数据
     */
    public List<User> searchValue(String realName) {
        try {
            String sql = "SELECT * FROM `user` WHERE username LIKE ? OR real_name like?";
            return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(User.class), "%" + realName + "%", "%" + realName + "%");
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 分页查询
     *
     * @param realName
     * @param pageSize
     * @param startPage
     */
    public List<User> findByPage(String realName, Integer pageSize, Integer startPage) {
        try {
            String sql = "SELECT * FROM `user` WHERE username LIKE ? OR real_name like ? LIMIT ?,?";
            return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(User.class), "%" + realName + "%", "%" + realName + "%", startPage, pageSize);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 计算总记录数
     *
     * @return
     */
    public Integer countByUser(String realName) {
        try {
            String sql = "SELECT COUNT(*) FROM `user` WHERE username LIKE ? OR real_name like ? ";
            return jdbcTemplate.queryForObject(sql, Integer.class, "%" + realName + "%", "%" + realName + "%");
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 查询所有部门
     *
     * @return
     */
    public List<Map<String, Object>> findDepartments() {
        try {
            String sql = "SELECT d.id, d.`name`,count(u.dept_id) count\n" +
                    "FROM `dept` d LEFT JOIN `user` u \n" +
                    "ON d.id = u.dept_id\n" +
                    "GROUP BY d.id ";
            return jdbcTemplate.queryForList(sql);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 查询所有部门下的所有员工
     *
     * @return
     */
    public List<User> findByUser(String deptId) {
        try {
            String sql = "SELECT * FROM user u\n" +
                    "WHERE u.dept_id = ? ";
            return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(User.class), deptId);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 查询用户的关注数
     *
     * @return
     */
    public Integer myself(Long loginUserId) {
        try {
            String sql = "SELECT count(*) focus  FROM userfocus  \n" +
                    "WHERE user_id = ? ";
            return jdbcTemplate.queryForObject(sql, Integer.class, loginUserId);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    public User findByUserDetail(Long userID) {
        try {
            String sql = "SELECT * FROM `user`\n" +
                    "WHERE id = ? ";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), userID);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 查看用户的关注数
     *
     * @param userID
     * @return
     */
    public Integer countUser(Long userID) {
        try {
            String sql = "SELECT count(*) focus  FROM userfocus  \n" +
                    "WHERE user_id = ? ";
            return jdbcTemplate.queryForObject(sql, Integer.class, userID);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 被看数加1
     *
     * @param userID
     */
    public void addLock(Long userID) {
        try {
            String sql = "UPDATE `user` \n" +
                    "SET look = look +1 \n" +
                    "WHERE id=? ";
            jdbcTemplate.update(sql, userID);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
        }
    }

    /**
     * 查询所属部门
     *
     * @param
     * @return
     */
    public List<Dept> findByDept() {
        try {
            return jdbcTemplate.query("select * from dept", new BeanPropertyRowMapper<>(Dept.class));
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 更新数据
     */
    public void update(User user) {
        jdbcTemplate.update(
                "update user set real_name=?,age=?,phone=?,gender=?,info=?,is_secret=?,dept_name=?,dept_id=? where id=?",
                user.getRealName(),
                user.getAge(),
                user.getPhone(),
                user.getGender(),
                user.getInfo(),
                user.getIsSecret(),
                user.getDeptName(),
                user.getDeptId(),
                user.getId());
    }

    public void uploadPic(User user) {
        jdbcTemplate.update(
                "update user set pic=? where id=?",
                user.getPic(),
                user.getId());
    }

    /**
     * 查询登录用户的关注信息
     *
     * @param id
     * @return
     */
    public List<Long> findByFocus(Long id) {
        String sql = "SELECT uf.user_focus_id FROM userfocus uf \n" +
                "WHERE user_id = ?";

        List<Long> userFoucsList = jdbcTemplate.query(sql, new RowMapper<Long>() {
            @Override
            public Long mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getLong("user_focus_id");
            }
        },id);
        return userFoucsList;
    }

    /**
     * 查看分页信息
     * @param startPage
     * @param userID
     * @return
     */
    public List<User> findPageFocusPage(Integer startPage, Long userID,Integer pageSize) {
        try {
            String sql = "SELECT * FROM `user` \n" +
                    "WHERE id IN(SELECT uf.user_focus_id FROM userfocus uf\n" +
                    "WHERE uf.user_id =?)\n" +
                    "LIMIT ?,? ";
            return jdbcTemplate.query
                    (sql, new BeanPropertyRowMapper<>(User.class), userID, startPage, pageSize);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 计算总页数
     * @param userID
     * @return
     */
    public Integer findByCount(Long userID) {
        try {
            String sql = "SELECT count(*) FROM userfocus uf\n" +
                    "WHERE uf.user_id = ? ";
            return jdbcTemplate.queryForObject(sql, Integer.class, userID);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }

    }

    /**
     * 查看有无这条数据
     * @param userId
     * @param id
     * @return
     */
    public Integer isFocus(String userId, Long id) {
        try {
            String sql = "SELECT count(*) has FROM userfocus uf\n" +
                    "WHERE uf.user_id = ? AND uf.user_focus_id = ?";
            return jdbcTemplate.queryForObject(sql, Integer.class, id, userId);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 删除这条数据
     * @param userId
     * @param id
     */
    public void removeFoucs(String userId, Long id) {
        jdbcTemplate.update(
                "DELETE FROM userfocus uf\n" +
                        "WHERE uf.user_id = ? AND uf.user_focus_id =?",
                id,
                userId);
    }

    /**
     * 添加数据
     * @param userId
     * @param id
     */
    public void addFocus(String userId, Long id) {
        jdbcTemplate.update(
                "INSERT INTO userfocus\n" +
                        " VALUES(?,?)",
                id,
                userId);
    }

    /**
     * 所有文章 ＋分页
     * @return
     */
    public List<Article> findPageAllArticlePage(Integer startPage,Integer pageSize) {
        try {
            String sql = "SELECT *FROM article\n" +
                    "LIMIT ?,? ";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Article.class), startPage, pageSize);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 文章总数
     * @return
     */
    public Integer findByArticleCount() {
        try {
            String sql = "SELECT COUNT(*) count from article ";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 搜索文章
     * @param startPage
     * @param pageSize
     * @param articleName
     * @return
     */
    public List<Article> findPageLikeArticle(Integer startPage, Integer pageSize, String articleName) {
        try {
            String sql = "SELECT * FROM article a\n" +
                    "WHERE a.title like ? or a.content like ?\n" +
                    "order by publish_date desc LIMIT ?,? ";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Article.class),
                    "%"+articleName+"%","%"+articleName+"%", startPage, pageSize);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 搜索文章总数
     * @param articleName
     * @return
     */
    public Integer findPageLikeArticleCount(String articleName) {
        try {
            String sql = "SELECT COUNT(*) count from article a\n" +
                    "WHERE a.title like ? or a.content like ? ";
            return jdbcTemplate.queryForObject(sql, Integer.class, "%"+articleName+"%","%"+articleName+"%");
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 插入文章
     */
    public void addArticle(Article article) {
        jdbcTemplate.update("insert into article values(null,?,?,?,?,?,?)",
                article.getTitle(),
                article.getContent(),
                article.getBrowseCount(),
                article.getPublishDate(),
                article.getPublishRealName(),
                article.getUserId()
        );
    }

    /**
     * 收藏文章信息
     * @param startPage
     * @param pageSize
     * @param userId
     * @return
     */
    public List<Article> findPageSaveArticle(Integer startPage, Integer pageSize, Long userId,String title) {
        try {
            String sql = "SELECT * FROM article a\n" +
                    "WHERE a.id in(SELECT f.a_id from favorite f\n" +
                    "WHERE f.u_id = ?) \n" +
                    "AND (a.title like ? or a.content like ?)\n" +
                    "order by publish_date desc LIMIT ?,? ";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Article.class),
                    userId,"%"+title+"%","%"+title+"%", startPage, pageSize);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }

    }

    /**
     * 收藏文章总数
     * @param userId
     * @return
     */
    public Integer findPageSaveArticleCount(Long userId,String title) {
        try {
            String sql = "SELECT COUNT(*) count FROM article a\n" +
                    "                    WHERE a.id in(SELECT f.a_id from favorite f\n" +
                    "                    WHERE f.u_id = ?) \n" +
                    "                    AND (a.title like ? or a.content like ?) ";
            return jdbcTemplate.queryForObject(sql, Integer.class, userId,"%"+title+"%","%"+title+"%");
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 查找文章信息
     * @param articleId
     */
    public Article findByArticleInfo(Long articleId) {
        try {
            String sql = "SELECT * FROM article WHERE id = ?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Article.class), articleId);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 是否收藏这篇文章
     * @param articleId
     * @param id
     * @return
     */
    public Integer saveArticle(Long articleId, Long id) {
        try {
            String sql = "SELECT count(1) count FROM favorite\n" +
                    "WHERE u_id = ? AND a_id =? ";
            return jdbcTemplate.queryForObject(sql, Integer.class, id,articleId);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 浏览量＋1
     * @param articleId
     */
    public void addBrowseCount(Long articleId) {
        jdbcTemplate.update("UPDATE article\n" +
                        "SET browse_count = browse_count + 1\n" +
                        "WHERE id = ?",
               articleId
        );
    }

    /**
     * 收藏次数
     * @param articleId
     * @return
     */
    public Integer saveArticleCount(Long articleId) {
        try {
            String sql = "SELECT count(*) count FROM favorite f\n" +
                    "WHERE f.a_id = ? ";
            return jdbcTemplate.queryForObject(sql, Integer.class,articleId);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }

    }

    /**
     * 我关注的人里关注这篇文章的人
     * @param articleId
     * @param id
     * @return
     */
    public List<User> addAndSave(Long articleId, Long id) {
        try {
            String sql = "SELECT * FROM `user`\n" +
                    "WHERE id IN (SELECT u_id FROM favorite f\n" +
                    "WHERE f.a_id = ?\n" +
                    "AND f.u_id IN (SELECT uf.user_focus_id focus FROM userfocus uf\n" +
                    "WHERE uf.user_id = ?)) ";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class),
                    articleId,id);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }

    }

    public void deleteArticle(Long articleId, Long id) {
        jdbcTemplate.update("DELETE from favorite\n" +
                        "WHERE u_id = ? AND a_id = ?",
                id,articleId
        );

    }

    public void add(Long articleId, Long id) {
        jdbcTemplate.update("INSERT INTO favorite \n" +
                        "VALUES(?,?)",
                id,articleId
        );
    }
}
